package DAO;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {
	//private String TABLE_NAME;
	
	public DBHelper(Context context, String name, CursorFactory cursorFactory, int version)
	{
		super(context, name, cursorFactory, version);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db)
	{
		//创建数据库后，对数据库的操作
		createUserTable(db);
		createSpeedDetail(db);
		createDaySummary(db);
		createPerRunning(db);
		createMonthAndWeekReport(db);
		createTrackingDetail(db);
		createTableStatus(db);
		createStepsTable(db);
		createYearTable(db);
		createSpeedDayTable(db);
		createPRunningDayTable(db);
		createTrackingDayTable(db);
		createPerRunningTodayTable(db);
		createTrackingTodayTable(db);
		createSpeedTodayTable(db);
		//insertTest(db);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		//更改数据库版本的操作

	}
	
	@Override
	public void onOpen(SQLiteDatabase db)
	{
		//每次成功打开数据库后首先被执�?
		super.onOpen(db);
	}
	
	public Cursor query(String sql, String[] args)
	{
		SQLiteDatabase db = this.getReadableDatabase();
		//insertTest(db);
		Cursor cursor = db.rawQuery(sql, args);
		return cursor;
	}
	
/*	String tName = "XFIT_Day_Summary";   //表名
	ContentValues cValue = new ContentValues();
	int i = 1;
	cValue.put("DaySummaryID", String.valueOf(i));
	cValue.put("SummaryDate", "'2011-11-08 00:00:00.000'");
	cValue.put("UserID", "91");
	cValue.put("SportTime", "3060.00");
	cValue.put("Calorie", "0.2269900000");
	cValue.put("Distance", "112.4151949025"); //插入的行信息*/
	
	public long executeInsert(ContentValues cValue, String tName)
	{
		SQLiteDatabase db = this.getWritableDatabase();
		long result = db.insert(tName, null, cValue);
		return result;
	}
	
	private void createYearTable(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  yearTable "
+"("
		+"     ID            INTEGER PRIMARY KEY AUTOINCREMENT,"
		+"     UserID            INTEGER not null,"
		+"    Syear 			varchar(20) NULL"
+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createPerRunningTodayTable(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  PerRunningTodayTable "
					+"("
					+"ID          		INTEGER PRIMARY KEY AUTOINCREMENT,"
					+"RunningID          INTEGER not null,"
					+"UserID            INTEGER not null,"
					+"StartTime         DATETIME,"
					+"EndTiome           DATETIME,"
					+"SportTime         decimal(18,2),"
					+"Calorie           decimal(18,10),"
					+"Distance          decimal(18,10),"
					+"IsRunning         char(1)"
					+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createTrackingTodayTable(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  TrackingTodayTable "
+"("
		+"     ID            INTEGER PRIMARY KEY AUTOINCREMENT,"
		+"     Track_ID      INTEGER NOT NULL,"
		+"    Track_Date    datetime NULL,"
		+"    TimeSpan      numeric(18,3) NULL,"
		+"    UserID        INTEGER NOT NULL,"
		+"    Steps         INTEGER NULL,"
		+"    Strength      INTEGER NULL,"
		+"    Calculated_Flag     char(1) NULL,"
		+"    Start_DateTime      datetime NULL,"
		+"    Client_VerNumber    varchar(100) NULL,"
		+"    Calorie             Decimal(18,10) NULL"
+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void  createSpeedTodayTable(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  SpeedTodayTable "
+"("
		+"     ID            INTEGER PRIMARY KEY AUTOINCREMENT,"
		+"     SpeedTraceID      INTEGER not null,"
		+"    UserID            INTEGER not null,"
		+"    RunningID         INTEGER not null,"
		+"    TrackDate         DATETIME,"
		+"    TimeSpan          decimal(18,2),"
		+"    AverageSpeed      decimal(18,10)"
		
+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createSpeedDayTable(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  speedDayTable "
+"("
		+"     ID            INTEGER PRIMARY KEY AUTOINCREMENT,"
		+"     UserID            INTEGER not null,"
		+"    SpeedDay 			varchar(20) NULL"
+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createPRunningDayTable(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  pRunningDayTable "
+"("
		+"     ID            INTEGER PRIMARY KEY AUTOINCREMENT,"
		+"     UserID            INTEGER not null,"
		+"    RunningDay 			varchar(20) NULL"
+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createTrackingDayTable(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  trackingDayTable "
+"("
		+"     ID            INTEGER PRIMARY KEY AUTOINCREMENT,"
		+"     UserID            INTEGER not null,"
		+"    trackingDay 			varchar(20) NULL"
+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createUserTable(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  XFIT_User_Info "
+"("
		+"     ID            INTEGER PRIMARY KEY AUTOINCREMENT,"
		+"     UserID            INTEGER not null,"
		+"     User_Email         varchar(40),"
		+"     Password         varchar(40),"
		+"    User_Weight       NUMERIC,"
		+"    User_Height       NUMERIC,"
		+"    User_Birthday     varchar(20),"
		+"    User_Gender       char(10),"
		+"   User_Adjustment_Data decimal(18,2),"
		+"    User_HardwareID   varchar(300),"
		+"   User_LastTimeD    	datetime,"
		+"    User_LastUploadDate varchar(200),"
		+"    StreetAddress   varchar(200)"
+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
		
	}
	
	private void createStepsTable(SQLiteDatabase db)
	{
		try
		{
		String sql = "create table  Steps_Detail"
				+"("
						+"     ID            INTEGER PRIMARY KEY AUTOINCREMENT,"
						+"     UserID        INTEGER not null,"
						+"     TYear         INTEGER not null,"
						+"     TMonth         INTEGER not null,"
						+"     TDay           INTEGER not null,"
						+"     NumOfTwoHours INTEGER not null,"
						+"     NumOf30s      INTEGER not null,"
						+"     TimeD         varchar(20) not null,"
						+"     Steps         INTEGER not null"
				+");";
		Log.i("wxs:createDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
		
	}
	
	private void createTableStatus(SQLiteDatabase db)
	{
		try
		{
			String sql = "create table  UpdateStatus "
		+"("
		+"     ID         INTEGER PRIMARY KEY AUTOINCREMENT,"
		+"    TableName   varchar(40),"
		+"    TimeD       varchar(20)"
		+");";
			
		Log.i("wxs:mk     oi  ioiocreateDB=", sql);
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createSpeedDetail(SQLiteDatabase db)
	{
		try
		{
		String sql = "create table  XFIT_Speed_Detail"
				+"("
				+"ID      			INTEGER PRIMARY KEY AUTOINCREMENT,"
				+"SpeedTraceID      INTEGER not null,"
				+"UserID            INTEGER not null,"
				+"RunningID         INTEGER not null,"
				+"TrackDate         DATETIME,"
				+"TimeSpan          decimal(18,2),"
				+"AverageSpeed     i  decimal(18,10)"
				+");";
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createDaySummary(SQLiteDatabase db)
	{
		String sql = "create table  XFIT_Day_Summary"
				+"("
				+"ID      			INTEGER PRIMARY KEY AUTOINCREMENT,"
				+"DaySummaryID      INTEGER not null,"
				+"SummaryDate       DATETIME,"
				+"UserID            INTEGER,"
				+"SportTime         decimal(18,2),"
				+"Calorie           decimal(18,10),"
				+"Distance          decimal(18,10)"
				+");";
		try
		{
			db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createPerRunning(SQLiteDatabase db)
	{
		try
		{
		String sql = "create table  XFIT_Per_Running"
				+"("
				+"ID          		INTEGER PRIMARY KEY AUTOINCREMENT,"
				+"RunningID          INTEGER not null,"
				+"UserID            INTEGER not null,"
				+"StartTime         DATETIME,"
				+"EndTime           DATETIME,"
				+"SportTime         decimal(18,2),"
				+"Calorie           decimal(18,10),"
				+"Distance          decimal(18,10),"
				+"IsRunning         char(1)"
				+");";
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createMonthAndWeekReport(SQLiteDatabase db)
	{
		try
		{
		String sql = "create table  MonthWeekYearReport"
				+"("
				+"ID                INTEGER PRIMARY KEY AUTOINCREMENT,"
				+"StartDate         DATETIME,"
				+"EndDate           DATETIME,"
				+"WeekMonthYear     INTEGER,"
				+"UserId            INTEGER,"
				+"ShowType          INTEGER"
				+");";
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createYearReport(SQLiteDatabase db)
	{
		try
		{
		String sql = "create table  YearReport"
				+"("
				+"ID                INTEGER PRIMARY KEY AUTOINCREMENT,"
				+"RMonth            INTEGER,"
				+"Calorie           Decimal(18,10),"
				+"Distance          Decimal(18,10),"
				+"RYear             INTEGER,"
				+"UserId            INTEGER"
				+");";
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	private void createTrackingDetail(SQLiteDatabase db)
	{
		try
		{
		String sql = "create table  XFIT_Tracking_Detail"   
				+"("
				+"Track_ID      INTEGER PRIMARY KEY AUTOINCREMENT,"
				+"Track_Date    datetime NULL,"
				+"TimeSpan      numeric(18,3) NULL,"
				+"UserID        INTEGER NOT NULL,"
				+"Steps         INTEGER NULL,"
				+"Strength      INTEGER NULL,"
				+"Calculated_Flag     char(1) NULL,"
				+"Start_DateTime      datetime NULL,"
				+"Client_VerNumber    varchar(100) NULL,"
				+"Calorie             Decimal(18,10) NULL"
				+");";
		db.execSQL(sql);
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
	}
	
	
	
//	private void create20RunningTable(SQLiteDatabase db)
//	{
//		try
//		{
//		String sql = "create table  Running_20"
//				+"("
//				+"ID                INTEGER PRIMARY KEY AUTOINCREMENT,"
//				+"RMonth            INTEGER,"
//				+"Calorie           Decimal(18,10),"
//				+"Distance          Decimal(18,10),"
//				+"RYear             INTEGER,"
//				+"UserId            INTEGER"
//				+");";
//		db.execSQL(sql);
//		}
//		catch (SQLException ex)
//		{
//			Log.i("error:", ex.getMessage());
//		}
//	}
	
	public void insertTest(SQLiteDatabase db)
	{
		String[] StartTime={
		"2011-11-07 11:22:00.000",
		"2011-11-07 12:22:00.000",
		"2011-11-07 13:22:00.000",
		"2011-11-07 14:22:00.000",
		"2011-11-07 15:22:00.000",
		"2011-11-07 17:22:00.000"};
		
		String[] EndTime={
		"2011-11-07 11:24:00.000",
		"2011-11-07 12:25:00.000",
		"2011-11-07 13:26:00.000",
		"2011-11-07 14:23:00.000",
		"2011-11-07 15:26:00.000",
		"2011-11-07 17:27:00.000"};
		
		String[] SportTime={
				"120",
				"180",
				"240",
				"60",
				"240",
				"300"
		};
		
		String[] UserID={
		"91",
		"91",
		"91",
		"91",
		"91",
		"91"};
		
		String[] Calorie={
		"148.4151949025",
		"357.1488647348",
		"1314.0432675243",
		"7.2752546520",
		"968.6009489081",
		"256.1551024319"};
		
		String[] Distance={
				"0.023",
				"0.004",
				"0.023",
				"0.025",
				"0.026",
				"0.027"
		};
		//db.beginTransaction();
		try
		{
		for (int i = 0; i < UserID.length; i++)
		{
			/*"RunningID          INTEGER not null,"
				+"UserID            INTEGER not null,"
				+"StartTime         DATETIME,"
				+"EndTime           DATETIME,"
				+"SportTime         decimal(18,2),"
				+"Calorie           decimal(18,10),"
				+"Distance          decimal(18,10),"
				+"IsRunning         char(1)"*/
			String sql = "insert into XFIT_Per_Running(RunningID,UserID,StartTime,EndTime,SportTime,Calorie,Distance,IsRunning) values("+String.valueOf(i+1)+","+UserID[i]+",'"+StartTime[i]+"','"+EndTime[i]+"',"+SportTime[i]+","+Calorie[i]+","+Distance[i]+",'N')";
			db.execSQL(sql);
		}
		}
		catch (SQLException ex)
		{
			Log.i("error:", ex.getMessage());
		}
		finally
		{
			//db.endTransaction();
			
		}
		//db.execSQL("insert into XFIT_Day_Summary values(2,'2011-11-08 00:00:00.000',91,3060.00,112.4151949025,0.2269900000);");
	}
}
